*****************
* Preparations: *
*****************

	clear all
	set more off 
	global path "Q:\SSB_skatt_boligomsetning\Deposits\do_files\replication kit" // path 
	cd "$path"
	
********************************	
* Creating additional folders: *
********************************

	cap mkdir "$path/tables"
	cap mkdir "$path/figures"
	cap mkdir "$path/data"

	
************************************
* Loading and XT-Setting the Data: *
************************************
	
	use "$path/data/main_data.dta", replace 
	xtset relid year	
	
	
*********************************************	
* Generating required additional variables: *
*********************************************

	egen total_unempl = total(unemployed), by(hhid)	 								// Number of unemployment spells in our data per household	
	gen hasboth = (debt > 0 & depositvol > 0)										// Dummy for relationships with both loans and deposits
	gen deposits = depositvol														
	gen existing_dep_l5 =  (L.deposits > 0  & L.deposits != .) | (L2.deposits > 0  & L2.deposits != .) | (L3.deposits > 0  & L3.deposits != .)  | (L4.deposits > 0  & L4.deposits != .) | (L5.deposits > 0  & L5.deposits != .) 	// Dummy for whether the household has been an existing depositor at the bank 	
	egen number_banks_hhid = count(bankid), by(hhid year)							// Count number of banks per household
	gen AgedSub30 = (year - birthyear) <= 30 										// Is age under 30?
	tabulate AgedSub30, generate(agegroup) 											// Generate education groups based on age under 30
	egen deprate_bank = mean(deprate), by(bankid year)								// Average deposit rate for the bank
	egen max_deprate = max(deprate_bank), by(year) 									// Maximum deposit rate offered by each bank 
	gen deprate_diff = max_deprate - deprate_bank 									// Difference between actual deposit rate and the maximum rate offered by each bank 
	gen deprate_lost = max_deprate - deprate 										// Deposit rate lost relative to maximum value, in pp.
	gen depinc_lost = deprate_lost * ave_dep										// Deposit income lost (deposit rate lost \times deposit volumes) 
	gen ln_depinc_lost = ln(depinc_lost)											// Take logarithm 
	gen deposit = ( depositvol>0 )													// Dummy for positive deposit volumes
	gen default = 0																	// Dummy for default		
	bys relid (year): replace default = 1 if gjeldsrenter == 0 & (L.loanvol > 0.01 & L.loanvol != .) // Default defined as not paying interest, while having non-zero outstanding loan amounts
	bys relid (year): gen count = _n
	bys relid (year): gen finaldebt_tmp = debt if count == _N
	egen finaldebt = max(finaldebt_tmp), by(relid)									// Measure of debt in last period of relationship
	gen ln_finaldebt = ln(finaldebt)
	bysort relid: egen meanloan = mean(debt)
	bysort relid: egen meandepvol =  mean(depositvol)	
	gen relconv = meanloan / meandepvol
	sum relconv, det 
	bysort bankid: egen bankconv = wtmean(relconv), weight(meandepvol)				// Conversion measure (average loans per average depositsl)

	foreach mpower of varlist hhi_deposit hhi_loan w_hhi_dep w_hhi_loans w_mshare_dep w_mshare_loans bankconv{
		quietly sum `mpower', det													// Binary market power measures at the bank-level 
		gen `mpower'_I = `mpower' >= r(p50)
		}


	bys relid (year): gen new_loan = (debt > 0 & L.debt == 0)						// Tag new loans
	bysort relid (year): gen tenure = _n											// Tenure
	tab educ, gen(educgroup)														// Education groups
	egen max_educ  = max(educ), by(hhid)											// Maximum education level per household
	gen university = max_educ>=3													// University dummy
	egen med_wealth = median(gross_wealth), by(year)								// Median wealth
	egen med_inc    = median(total_income), by(year)  								// Median total income
	gen  low_inc     = total_income <= med_inc										// Dummy for low income
	gen  low_wealth  = gross_wealth <= med_wealth									// Dummy for low wealth
	gen futureborrower = (new_loan == 1 | F.new_loan == 1 | F2.new_loan == 1 | F3.new_loan == 1 | F4.new_loan == 1 | F5.new_loan == 1 | F6.new_loan == 1 | F7.new_loan == 1 | F8.new_loan == 1 | F9.new_loan == 1 | F10.new_loan == 1 | F11.new_loan == 1 | F12.new_loan == 1 | F13.new_loan == 1 )
	gen switcher = 1 if new_loan == 1												// Tagging switchers
	replace switcher = 0 if switcher!=. & existing_dep==1		

	
***********************	
* Labeling variables: *
***********************

	label var existing_dep 		"I(Depositor)"
	label var ln_debt 			"Ln(Debt)"
	label var hashome3 			"I(Owns Real Estate)"
	label var ln_deposits 		"Ln(Deposits)"
	label var birthyear 		"Birth Year"
	label var pop_density 		"Pop. Density"
	label var number_banks_hhid "Number of Banks Used"
	label var has_unempl 		"Has ever received UI"
	label var hashome3			"I(Owns Real Estate)"
	label var loanrate 			"Loan Rate"
	label var loanspread_on 	"Loan Spread"

	
************************************************	
* Winsorization and other sample restrictions: *
************************************************

	winsor2 depinc_lost, cuts(0 95) replace											// Remove upper tail of deposit income lost to not overstate results due to measurement error 
	winsor2 number_banks_hhid, cuts(5 95) replace 									// Winsorize number of banks per hh_id 

	
*************************************
* FIGURE 1: Deposit spreads Europe: *
*************************************

* In separate do file as based on separate data.

	
*************************************
* FIGURE 2: Deposit spreads, Norway *
*************************************
	
	preserve
	// Compute average rates and spreads
	collapse deprate prate depspread [weight = ave_dep], by(year)	

* Plot and export everything
	twoway (line deprate year, lpattern(solid)) (line prate year, lpattern(shortdash)) (line depspread year, lpattern(longdash)), graphregion(color(white)) ytitle("%") xtitle("") legend(label(1 "Deposit Rate") label(2 "Policy Rate") label(3 "Deposit Spread")) title("Average Deposit Spreads over Time")
	graph export "$path/figures/agg_dep_rate.jpg", replace
	restore 


***********************************	
* FIGURE 3: Depositor Stickiness: *
***********************************

* Regression leads of depositor dummy on itself
	xtset relid year

	tempname results_stickiness
	postfile `results_stickiness' horizon coefa1 se1 using "$path/figures/results_stickiness.dta", replace
	
	forval f = 1/10 {
	reghdfe F`f'.deposit deposit, absorb(year##muniid)
	post `results_stickiness' (`f') (`=_b[deposit]') (`=_se[deposit]')
	}
	postclose `results_stickiness'
	
* Graph the results
	preserve
	use "$path/figures/results_stickiness", clear

	gen low1  = coefa1-1.96 * se1
	gen high1 = coefa1+1.96 * se1

	replace coefa1 = coefa1*100
	replace low1 = low1*100
	replace high1 = high1*100
	
	sort horizon
	twoway ///
	(line coefa1 horizon, lcolor(maroon)) ///
	(line low1   horizon, lpattern(dash) lcolor(black)) ///
	(line high1  horizon, lpattern(dash) lcolor(black)), ///
	ytitle("Deposit Relationship Propensity") xtitle("Years since first deposit relationship observation") title("Depositor Stickiness", color(black)) xlabel(1(2)13 14, nogrid) graphregion(color(white)) 
	
	graph export "$path/figures/depositor_stickiness.jpg", replace
	restore	


********************************************
* FIGURE 4: Depositor-borrower conversion: *
********************************************

	tempname results1
	postfile `results1' horizon coefa1 se1 nobs using "$path/figures/results1.dta", replace	
	xtset relid year
	
* Run regression of leads on new_loan on the existing depositor dummy
	foreach num of numlist 0/13{
		reghdfe F`num'.new_loan existing_dep, absorb(i.muniid#i.agebingroup#i.salaryquarter#i.educ)
		post `results1' (`num') (`=_b[existing_dep]') (`=_se[existing_dep]') (`=e(N)')
		}
	postclose `results1'

	
* Use the estimated coefficients obtained above and plot them 
	preserve
	use "$path/figures/results1", clear

	gen low1=coefa1-se1
	gen high1=coefa1+se1
	replace horizon = horizon + 1
	gen cum_sum = .
	replace cum_sum = coefa1[1] in 1

	foreach num of numlist 2/14{
	replace cum_sum = cum_sum[`num'-1] + coefa1[`num'] in `num'
	}

	replace cum_sum = cum_sum*100
	replace coefa1 = coefa1*100
	replace low1 = low1*100
	replace high1 = high1*100

	sort horizon
	twoway ///
	(line coefa1 horizon, lcolor(maroon)) ///
	(line low1 horizon, lpattern(dash) lcolor(black)) ///
	(line high1 horizon, lpattern(dash) lcolor(black)) ///
	(line cum_sum horizon, lcolor(black) lwidth(thick) yaxis(2)) , ///
	ytitle("Fraction becoming a borrower") xtitle("Years with observed deposit account") title("Existing depositors and subsequent borrowing", color(black)) ylabel(,nogrid) xlabel(1(2)13 14, nogrid) graphregion(color(white)) yline(0, lcolor(ltbluishgray)) graphregion(color(white)) ytitle("Cumulative probability", axis(2)) legend(order(1 	"Marginal effect" 4 "Cumulative probability (right)"))	
		
	graph export "$path/figures/1a_depositor_conversion.jpg", replace
	restore


**********************************************
* FIGURE 5: Deposit spreads by account type: *
**********************************************

	* In separate do file, as based on different (Finansportalen) data.	
	
	
*****************************************************************
* FIGURE 6: Fraction of bank relationships started before move: *
*****************************************************************

	preserve 
	
	clear all
	input sameplace withinmovers acrossmovers 
	52.68 4.70 42.62
	end
	label var sameplace "... in current postcode"
	label var withinmovers "...in different postcode but same municipality"
	label var acrossmovers "...in different municipality"
	
	graph pie sameplace withinmovers acrossmovers, title("Bank relationship with new lender started ...") plabel(_all percent) graphregion(color(white))
	
	graph export "$path/figures/bankretention_across_moves.tiff", replace
	
	restore

	
***********************************
* FIGURE 7: Deposit spreads beta: *
***********************************
	
	preserve

* Compute average rates and spreads
	collapse deprate prate depspread rprate [weight = ave_dep], by(year)	
	tsset year 

* Deposit Spread Beta Against Nominal Policy Rate:
	binscatter depspread prate, xtitle("Policy rate (%)") ytitle("Deposit Spread (%)") title("Deposit Spread and Nominal Policy Rate")
	graph export "$path/figures/depspread_prate.jpg", replace

* Deposit Spread Beta Against Real Policy Rate:	
	binscatter depspread rprate, xtitle("Real Policy Rate (%)") ytitle("Deposit Spread (%)") title("Deposit Spread and Real Policy Rate")
	graph export "$path/figures/depspread_rprate.jpg", replace

* Deposit Spread Beta Against Nominal Policy Rate Changes:	
	gen d_prate = D.prate // generate change in policy rate 
	binscatter depspread d_prate, xtitle("Change in Policy Rate (%)") ytitle("Deposit Spread (%)") title("Deposit Spread and Year-on-Year Policy Rate Changes")
	graph export "$path/figures/d_depspread_prate.jpg", replace

	restore


********************************
* TABLE 1: Summary statistics: * 
********************************

* Export summary statistics 
	eststo clear

	estpost tabstat prate deprate depspread loanrate loanspread_on depositvol loanvol debt finaldebt deprate_lost depinc_lost number_years_relid number_banks_hhid AgedSub30 parent retired educgroup1 educgroup2 educgroup3 educgroup4 hhi_deposit hhi_loan w_hhi_dep w_hhi_loans w_mshare_dep w_mshare_loans real_assets pop_density 	 number_banks_hhid hashome3, listwise statistics(count mean sd min max)
	
	esttab using "$path/tables/sumstats", scsv varwidth(100) replace cells(prate deprate depspread loanrate loanspread_on depositvol loanvol debt finaldebt deprate_lost depinc_lost number_years_relid number_banks_hhid AgedSub30 parent retired educgroup1 educgroup2 educgroup3 educgroup4 hhi_deposit hhi_loan w_hhi_dep w_hhi_loans w_mshare_dep w_mshare_loans real_assets pop_density number_banks_hhid hashome3)
	
	
*******************************************
* TABLE 2: Determinants of deposits lost: *
*******************************************

	eststo clear
	eststo: reghdfe deprate_lost parent retired ln_inc ln_wealth ln_deposits AgedSub30, noa cluster(relid)
	eststo: qui reghdfe ln_depinc_lost parent retired ln_inc ln_wealth ln_deposits AgedSub30, noa cluster(relid)
	eststo: qui reghdfe deprate_lost parent retired ln_inc ln_wealth ln_deposits AgedSub30, absorb(i.muniid#i.salaryquarter#i.educ) cluster(relid)
	eststo: qui reghdfe ln_depinc_lost parent retired ln_inc ln_wealth ln_deposits AgedSub30, absorb(i.muniid#i.salaryquarter#i.educ) cluster(relid)
	esttab using "$path/tables/deposit_inc_lost", csv b(2) se(2) nogaps replace r2 star(* 0.1 ** 0.05 *** 0.01)


**************************	
* TABLE 3: Loan pricing: *
**************************

* Without controls
	eststo clear
	eststo: reghdfe loanrate      existing_dep_l5, cluster(bankid##year) absorb(bankid##year)
	estadd local bankyear "Y"
	estadd local educmuniyear "N"
	eststo: reghdfe loanspread_on existing_dep_l5, cluster(bankid##year) absorb(bankid##year)
	estadd local bankyear "Y"
	estadd local educmuniyear "N"

* With HasRE:
	eststo: reghdfe loanrate      existing_dep_l5 ln_debt hashome2 ln_deposits birthyear has_unempl pop_density number_banks_hhid, cluster(bankid##year) absorb(bankid##year educ#muniid#year)
	estadd local bankyear "Y"
	estadd local educmuniyear "Y"
	eststo: reghdfe loanspread_on existing_dep_l5 ln_debt hashome2 ln_deposits birthyear has_unempl pop_density number_banks_hhid, cluster(bankid##year) absorb(bankid##year educ#muniid#year)
	estadd local bankyear "Y"
	estadd local educmuniyear "Y"

* With House Value:	
	eststo: reghdfe loanrate   existing_dep_l5 ln_debt ln_housevalue2 ln_deposits birthyear has_unempl pop_density number_banks_hhid, cluster(bankid##year) absorb(bankid##year educ#muniid#year)
	estadd local bankyear "Y"
	estadd local educmuniyear "Y"
	eststo: reghdfe loanspread_on existing_dep_l5 ln_debt ln_housevalue2 ln_deposits birthyear has_unempl pop_density number_banks_hhid, cluster(bankid##year) absorb(bankid##year educ#muniid#year)
	estadd local bankyear "Y"
	estadd local educmuniyear "Y"
	
* Tabulating it all:	
	esttab using "$path/tables/loanpricing.csv", csv b(2) se(2) nogaps replace r2 star(* 0.1 ** 0.05 *** 0.01) noomitted label s(N r2 bankyear educmuniyear, label("Observations" "R2" "Bank x Year FE" "Municipality x Education x Year FE"))  	
	eststo clear	
	

********************************************
* TABLE 4: Screening of future defaulters: *
********************************************	

* Investigate whether future defaults are priced in to a larger extent for existing depositors
	eststo clear	
	eststo: reghdfe loanrate  i.ever_default##i.existing_dep_l5 ln_debt ln_housevalue2 ln_deposits birthyear has_unempl pop_density number_banks_hhid, cluster(bankid##year) absorb(bankid##year educ#muniid#year)
	estadd local bankyear "Y"
	estadd local educmuniyear "Y"
	eststo: reghdfe loanspread_on i.ever_default##i.existing_dep_l5 ln_debt ln_housevalue2 ln_deposits birthyear has_unempl pop_density number_banks_hhid, cluster(bankid##year) absorb(bankid##year educ#muniid#year)
	estadd local bankyear "Y"
	estadd local educmuniyear "Y"
	esttab using "$path/tables/screening.csv", csv b(2) se(2) nogaps replace r2 star(* 0.1 ** 0.05 *** 0.01) noomitted label s(N r2 bankyear educmuniyear, label("Observations" "R2" "Bank x Year FE" "Municipality x Education x Year FE"))  
	
	
********************************************************************************	
* TABLE 5: Instrumental variables regression on depositor-borrower conversion: *
********************************************************************************

	preserve
	drop if sameplace==0

	eststo clear
* Without controls for current market shares	
	eststo: reghdfe   futureborrower existing_dep                       ,  absorb(i.muniid#i.agebingroup#i.salaryquarter#i.educ) // OLS
	eststo: reghdfe   futureborrower old_mshare_deposit                 ,  absorb(i.muniid#i.agebingroup#i.salaryquarter#i.educ) // RF
	eststo: reghdfe   existing_dep old_mshare_deposit             ,  absorb(i.muniid#i.agebingroup#i.salaryquarter#i.educ) // FS
	eststo: ivreghdfe futureborrower (existing_dep = old_mshare_deposit),  absorb(i.muniid#i.agebingroup#i.salaryquarter#i.educ) // IV

* With controls for current market shares
	eststo: reghdfe   futureborrower existing_dep                        mrkt_share_deposit,  absorb(i.muniid#i.agebingroup#i.salaryquarter#i.educ) // OLS
	eststo: reghdfe   futureborrower old_mshare_deposit                         mrkt_share_deposit,  absorb(i.muniid#i.agebingroup#i.salaryquarter#i.educ) // RF
	eststo: reghdfe   existing_dep old_mshare_deposit                         mrkt_share_deposit,  absorb(i.muniid#i.agebingroup#i.salaryquarter#i.educ) // FS
	eststo: ivreghdfe futureborrower (existing_dep = old_mshare_deposit) mrkt_share_deposit,  absorb(i.muniid#i.agebingroup#i.salaryquarter#i.educ) // IV
	esttab using "$path/tables/movers_exercise_2023m2d",  csv b(3) se(3) nogaps replace r2 star(* 0.1 ** 0.05 *** 0.01)	
	
	restore	
		
****************************************************
* TABLE 6: Switching as a function of bank tenure: *
****************************************************

	eststo clear
	eststo: reghdfe switcher            AgedSub30 low_wealth i.university tenure, noa cluster(relid)
	eststo: reghdfe switcher                      low_wealth  tenure, absorb(i.muniid#i.agebingroup#i.salaryquarter#i.educ) cluster(i.muniid#i.agebingroup#i.salaryquarter#i.educ)
	esttab using "$path/tables/switching_stayers", csv b(3) se(3) nogaps replace r2 star(* 0.1 ** 0.05 *** 0.01)	

	
**************************************************************************************************
* TABLE 7: Household and bank determinants of loan probability, loan amount and deposit pricing: *
**************************************************************************************************

	eststo clear
	eststo: qui reghdfe futureborrower parent retired ln_inc ln_wealth ln_deposits AgedSub30 number_banks_hhid hhi_deposit, noa cluster(relid)
	predict pr_borrower1, xb 													// Needed for bank-level regression in Table 8
	eststo: qui reghdfe ln_finaldebt   parent retired ln_inc ln_wealth  ln_deposits AgedSub30 number_banks_hhid hhi_deposit, noa cluster(relid)
	predict pr_lnfinaldebt1, xb													// Needed for bank-level regression in Table 8
	eststo: qui reghdfe depspread      parent retired ln_inc ln_wealth ln_deposits AgedSub30 number_banks_hhid hhi_deposit hasboth bankconv, noa cluster(relid)
	eststo: qui reghdfe futureborrower  parent retired ln_inc ln_wealth ln_deposits AgedSub30 number_banks_hhid hhi_deposit, absorb(i.muniid#i.salaryquarter#i.educ) cluster(relid)	
	eststo: qui reghdfe ln_finaldebt    parent retired ln_inc ln_wealth ln_deposits AgedSub30 number_banks_hhid hhi_deposit, absorb(i.muniid#i.salaryquarter#i.educ) cluster(relid) 	
	eststo: qui reghdfe depspread       parent retired ln_inc ln_wealth ln_deposits AgedSub30 number_banks_hhid hhi_deposit  hasboth bankconv, absorb(i.muniid#i.salaryquarter#i.educ)	cluster(muniid#salaryquarter#educ)
	esttab using "$path/tables/2_pricing", csv b(3) se(3) nogaps replace r2 star(* 0.1 ** 0.05 *** 0.01)


***************************************************************************************	
* TABLE 8: Deposit spread beta by predicted loan propensity and market concentration: *
***************************************************************************************

* Export predictions used for bank-level regressions in Table 8:
	preserve
	keep relid pr_*
	sum pr_lnfinaldebt1, det
	gen pr_I_finaldebt = (pr_lnfinaldebt1 >= r(p50))
	save "$path\data\predictions", replace	
	restore

* Merging in borrowing predictions for each relationship:	
	sort relid
	merge relid using "$path\data\predictions"
	tab _merge
	drop if _merge==2
	drop _merge
	
* Run regression
	eststo clear
	eststo: quietly reghdfe depspread prate, absorb(i.agebingroup#i.salaryquarter#i.educ) cluster(relid)
	foreach mpower of varlist hhi_deposit hhi_loan w_hhi_dep w_hhi_loans w_mshare_dep w_mshare_loans {
		replace `mpower' = `mpower'_I
		eststo: quietly reghdfe depspread c.prate##bankconv_I  c.prate##`mpower', absorb(i.agebingroup#i.salaryquarter#i.educ) cluster(relid)
		}	
	esttab using "$path/tables/depspread_beta_bankconv2", csv b(2) se(2) nogaps replace r2 noomitted	star(* 0.1 ** 0.05 *** 0.01)	

	


	
	
	
		
		
		